{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['_id', '_index', '_score', '_type', '_source.fileType',\n",
      "       '_source.fu_jian_lei_bie', '_source.ming_cheng', '_source.nums',\n",
      "       '_source.pid', '_source.pids', '_source.create_by',\n",
      "       '_source.create_by_name', '_source.create_time',\n",
      "       '_source.fu_jian_bei_zhu', '_source.fu_jian_da_xiao',\n",
      "       '_source.fu_jian_lu_jing', '_source.md5', '_source.remarks1',\n",
      "       '_source.update_by', '_source.update_by_name', '_source.update_time',\n",
      "       '_source.fu_jian_nei_rong', '_source.nei_rong_da_xiao',\n",
      "       '_source.bei_zhu', '_source.fu_jian_da_xiao_str',\n",
      "       '_source.fu_jian_ming_cheng', '_source.icon', '_source.lai_yuan',\n",
      "       '_source.lei_bie', '_source.pai_xu', '_source.path', '_source.remarks2',\n",
      "       '_source.sameMd5Count', '_source.sameMd5Str', 'name', 'dir', 'size',\n",
      "       'md5'],\n",
      "      dtype='object')\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 22208 entries, 0 to 22207\n",
      "Data columns (total 34 columns):\n",
      " #   Column                       Non-Null Count  Dtype  \n",
      "---  ------                       --------------  -----  \n",
      " 0   _id                          22208 non-null  object \n",
      " 1   _index                       22208 non-null  object \n",
      " 2   _score                       22208 non-null  int64  \n",
      " 3   _type                        22208 non-null  object \n",
      " 4   _source.fileType             2263 non-null   object \n",
      " 5   _source.fu_jian_lei_bie      22208 non-null  object \n",
      " 6   _source.nums                 22208 non-null  int64  \n",
      " 7   _source.pid                  22208 non-null  object \n",
      " 8   _source.pids                 2717 non-null   object \n",
      " 9   _source.create_by            16032 non-null  float64\n",
      " 10  _source.create_by_name       16032 non-null  object \n",
      " 11  _source.create_time          16032 non-null  float64\n",
      " 12  _source.fu_jian_lu_jing      21553 non-null  object \n",
      " 13  _source.remarks1             21553 non-null  object \n",
      " 14  _source.update_by            16032 non-null  float64\n",
      " 15  _source.update_by_name       16032 non-null  object \n",
      " 16  _source.update_time          16032 non-null  float64\n",
      " 17  _source.fu_jian_nei_rong     2065 non-null   object \n",
      " 18  _source.nei_rong_da_xiao     14 non-null     float64\n",
      " 19  _source.bei_zhu              2516 non-null   object \n",
      " 20  _source.fu_jian_da_xiao_str  2062 non-null   object \n",
      " 21  _source.fu_jian_ming_cheng   2062 non-null   object \n",
      " 22  _source.icon                 2062 non-null   object \n",
      " 23  _source.lai_yuan             2062 non-null   object \n",
      " 24  _source.lei_bie              2516 non-null   object \n",
      " 25  _source.pai_xu               2639 non-null   float64\n",
      " 26  _source.path                 2062 non-null   object \n",
      " 27  _source.remarks2             2062 non-null   object \n",
      " 28  _source.sameMd5Count         2639 non-null   float64\n",
      " 29  _source.sameMd5Str           2062 non-null   object \n",
      " 30  _source.ming_cheng           22208 non-null  object \n",
      " 31  _source.fu_jian_bei_zhu      21443 non-null  object \n",
      " 32  _source.fu_jian_da_xiao      21553 non-null  object \n",
      " 33  _source.md5                  21553 non-null  object \n",
      "dtypes: float64(7), int64(2), object(25)\n",
      "memory usage: 5.8+ MB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "import pandas as pd\n",
    "target_path = r'F:\\工作任务\\2024-09-27证据文件来源路径修改\\证据文件\\st_evidence_20240926_data(1).json'\n",
    "output_path = r'F:\\工作任务\\2024-09-27证据文件来源路径修改\\证据文件\\st_evidence_20240926_data.xlsx'\n",
    "base_file_path = r'F:\\工作任务\\2024-09-27证据文件来源路径修改\\证据文件\\result\\default_dyf_evidence_file.csv'\n",
    "json_df = pd.DataFrame()\n",
    "with open(target_path, 'r', encoding='utf-8') as f:\n",
    "    data = json.load(f)\n",
    "    json_df = pd.json_normalize(data)\n",
    "    # print(df.columns)\n",
    "    # print(df.shape[0])\n",
    "    # df.to_excel(output_path, index=False)\n",
    "# 读取基础表\n",
    "base_df = pd.read_csv(base_file_path,dtype=str,delimiter='\\t')\n",
    "base_df.drop_duplicates(subset=['name'], inplace=True)\n",
    "base_df = base_df[['name', 'dir','size','md5']]\n",
    "# 数据匹配\n",
    "result_df = pd.merge(json_df, base_df, left_on='_source.ming_cheng',right_on='name', how='left')\n",
    "result_df['name'] = result_df['name'].fillna(result_df['_source.ming_cheng'])\n",
    "result_df['dir'] = result_df['dir'].fillna(result_df['_source.fu_jian_bei_zhu'])\n",
    "result_df['size'] = result_df['size'].fillna(result_df['_source.fu_jian_da_xiao'])\n",
    "result_df['md5'] = result_df['md5'].fillna(result_df['_source.md5'])\n",
    "# 删除数数据\n",
    "print(result_df.columns)\n",
    "result_df = result_df.drop(columns=['_source.ming_cheng','_source.fu_jian_bei_zhu','_source.fu_jian_da_xiao','_source.md5'])\n",
    "result_df = result_df.rename(columns={'name':'_source.ming_cheng','dir':'_source.fu_jian_bei_zhu','size':'_source.fu_jian_da_xiao','md5':'_source.md5'})\n",
    "\n",
    "result_df.to_excel(output_path, index=False)\n",
    "result_json = result_df.to_json(orient='records',lines=True,force_ascii=False)\n",
    "with open(r'F:\\工作任务\\2024-09-27证据文件来源路径修改\\证据文件\\result\\st_evidence_20240926_data.json','w',encoding='utf-8') as json_file:\n",
    "    json_file.write(result_json)\n",
    "print(result_df.info())\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
